﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using Obj;

namespace DAL
{
    /// <summary>
    /// Summary description for SQLAutomovel
    /// </summary>
    public class SQLAutomovel : dataAccess
    {


        public bool Delete(int automovelID)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("automovel_Delete", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@automovelID", SqlDbType.Int).Value = automovelID;
                con.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        /// <summary>
        /// Método que tira a associação do automovel com a garagem. Utilizado quando for excluir uma garagem. 
        /// </summary>
        /// <param name="garagemID"></param>
        /// <returns></returns>
        public int UpdateByGaragem(int garagemID) 
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("automovel_UpdateByGaragem", con);
                cmd.Parameters.Add("@garagemID", SqlDbType.Int).Value = garagemID;
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                int ret = ExecuteNonQuery(cmd);
                return ret;
            }
        
        
        }

        public bool Update(objAutomovel automovel)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("automovel_Update", con);
                cmd.Parameters.Add("@Descricao", SqlDbType.NVarChar).Value = automovel.Descricao;
                cmd.Parameters.Add("@modeloID", SqlDbType.Int).Value = automovel.ModeloID;
                cmd.Parameters.Add("@garagemID", SqlDbType.Int).Value = automovel.GaragemID;
                cmd.Parameters.Add("@anoFabricacao", SqlDbType.SmallInt).Value = automovel.AnoFabricacao;
                cmd.Parameters.Add("@anoModelo", SqlDbType.SmallInt).Value = automovel.AnoModelo;
                cmd.Parameters.Add("@preco", SqlDbType.Decimal).Value = automovel.Preco;
                cmd.Parameters.Add("@blindado", SqlDbType.Bit).Value = automovel.Blindado;
                cmd.Parameters.Add("@quilometragem", SqlDbType.Int).Value = automovel.Quilometragem;
                cmd.Parameters.Add("@cambio", SqlDbType.NVarChar).Value = automovel.Cambio;
                cmd.Parameters.Add("@portas", SqlDbType.TinyInt).Value = automovel.Portas;
                cmd.Parameters.Add("@cor", SqlDbType.NVarChar).Value = automovel.Cor;
                cmd.Parameters.Add("@combustivel", SqlDbType.NVarChar).Value = automovel.Combustivel;
                cmd.Parameters.Add("@observacoes", SqlDbType.NVarChar).Value = automovel.Observacoes;
                cmd.Parameters.Add("@pasta", SqlDbType.NVarChar).Value = automovel.Pasta;
                cmd.Parameters.Add("@automovelID", SqlDbType.Int).Value = automovel.AutomovelID;
                cmd.Parameters.Add("@venda", SqlDbType.Bit).Value = automovel.Venda;
                cmd.Parameters.Add("@ativo", SqlDbType.Bit).Value = automovel.Ativo;           
                cmd.Parameters.Add("@vitrine", SqlDbType.Bit).Value = automovel.Vitrine;

                cmd.CommandType = CommandType.StoredProcedure;
               
                con.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);

            }
        }

        public int Insert(objAutomovel automovel)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("automovel_Insert", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Descricao", SqlDbType.NVarChar).Value = automovel.Descricao;
                cmd.Parameters.Add("@ativo", SqlDbType.Bit).Value = automovel.Ativo;
                cmd.Parameters.Add("@venda", SqlDbType.Bit).Value = automovel.Venda;
                cmd.Parameters.Add("@modeloID", SqlDbType.Int).Value = automovel.ModeloID;
                cmd.Parameters.Add("@garagemID", SqlDbType.Int).Value = automovel.GaragemID;
                cmd.Parameters.Add("@anoFabricacao", SqlDbType.SmallInt).Value = automovel.AnoFabricacao;
                cmd.Parameters.Add("@anoModelo", SqlDbType.SmallInt).Value = automovel.AnoModelo;
                cmd.Parameters.Add("@preco", SqlDbType.Decimal).Value = automovel.Preco;
                cmd.Parameters.Add("@blindado", SqlDbType.Bit).Value = automovel.Blindado;
                cmd.Parameters.Add("@quilometragem", SqlDbType.Int).Value = automovel.Quilometragem;
                cmd.Parameters.Add("@cambio", SqlDbType.NVarChar).Value = automovel.Cambio;
                cmd.Parameters.Add("@portas", SqlDbType.TinyInt).Value = automovel.Portas;
                cmd.Parameters.Add("@cor", SqlDbType.NVarChar).Value = automovel.Cor;
                cmd.Parameters.Add("@combustivel", SqlDbType.NVarChar).Value = automovel.Combustivel;
                cmd.Parameters.Add("@observacoes", SqlDbType.NVarChar).Value = automovel.Observacoes;
                cmd.Parameters.Add("@pasta", SqlDbType.NVarChar).Value = automovel.Pasta;
                cmd.Parameters.Add("@vitrine", SqlDbType.Bit).Value = automovel.Vitrine;
                cmd.Parameters.Add("@automovelID", SqlDbType.Int).Direction = ParameterDirection.Output;
                con.Open();
                int ret = ExecuteNonQuery(cmd);
                return (int)cmd.Parameters["@automovelID"].Value;
            }
        }

        public List<objAutomovel> SelectByGaragem(int garagemID)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("automovel_selectByGaragem", con);
                cmd.Parameters.Add("@garagemID", SqlDbType.Int).Value = garagemID;
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                return objLista(ExecuteReader(cmd));
            }
        }
        public List<objAutomovel> SelectAll()
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("automovel_selectAll", con);
                //cmd.Parameters.Add("@garagemID", SqlDbType.Int).Value = garagemID;
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                return objLista(ExecuteReader(cmd));
            }
        }
        public List<objAutomovel> SelectAll(objAutomovel automovel)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("automovel_selectAll", con);
                if(automovel.MarcaID != 0)
                    cmd.Parameters.Add("@MarcaID", SqlDbType.Int).Value = automovel.MarcaID;
                if(automovel.ModeloID!= 0)
                    cmd.Parameters.Add("@ModeloID", SqlDbType.Int).Value = automovel.ModeloID;
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                return objLista(ExecuteReader(cmd));
            }
        }
        public List<objAutomovel> SelectRelacionados(objAutomovel automovel)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("automovel_selectRelacionados", con);
                if (automovel.AutomovelID != 0)
                    cmd.Parameters.Add("@AutomovelID", SqlDbType.Int).Value = automovel.AutomovelID;               
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                return objLista(ExecuteReader(cmd));
            }
        }

        public List<objAutomovel> SelectVitrine() 
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("automovel_selectVitrine", con);
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                return objLista(ExecuteReader(cmd));
            }
        
        
        }
        
        public List<objAutomovel> SelectTop(objAutomovel automovel)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("automovel_selectTop", con);
                if (automovel.GaragemID != 0)
                {
                    cmd.Parameters.Add("@garagemID", SqlDbType.Int).Value = automovel.GaragemID;
                }
                if (automovel.MarcaID != 0)
                {
                    cmd.Parameters.Add("@MarcaID", SqlDbType.Int).Value = automovel.MarcaID;
                }
                if (automovel.ModeloID != 0)
                {
                    cmd.Parameters.Add("@ModeloID", SqlDbType.Int).Value = automovel.ModeloID;
                }
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                return objLista(ExecuteReader(cmd));
            }
        }
        public object CountByGaragem(int garagemID) 
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("automovel_CountByGaragem", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@garagemID", SqlDbType.Int).Value = garagemID;
                con.Open();
                return ExecuteScalar(cmd);
            }
        
        
        }

        public objAutomovel SelectByID(int automovelID)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("automovel_SelectByID", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@automovelID", SqlDbType.Int).Value = automovelID;
                con.Open();
                IDataReader reader = ExecuteReader(cmd);
                if (reader.Read())
                    return obj(reader);
                else
                    return null;
            }
        }
        public objAutomovel SelectByPasta(string Pasta)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("automovel_Select", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@pasta", SqlDbType.NVarChar).Value = Pasta;
                con.Open();
                IDataReader reader = ExecuteReader(cmd);
                if (reader.Read())
                    return obj(reader);
                else
                    return null;
            }
        }

        private objAutomovel obj(IDataReader reader)
        {
            /*implementacao necessária
             * instanciar o construtor com todos os parametros e não o construtor em branco. 
             * 
             */
            Int16 shortAux = 0;
            int intAux = 0;
            objAutomovel automovel      = new objAutomovel();
            automovel.Descricao         = reader["Descricao"].ToString();
            
            Int16.TryParse(reader["AnoFabricacao"].ToString(), out shortAux);
            automovel.AnoFabricacao     = shortAux;
            shortAux = 0;

            Int16.TryParse(reader["AnoModelo"].ToString(), out shortAux);
            automovel.AnoModelo = shortAux;
            shortAux = 0;

            automovel.Ativo             = (bool)reader["Ativo"];

             
            automovel.AutomovelID       = (int)reader["AutomovelID"];
            
            automovel.Blindado          = (bool)reader["Blindado"];
            automovel.Vitrine           = (bool)reader["Vitrine"];
            automovel.Cambio            = reader["Cambio"].ToString();
            automovel.Combustivel       = reader["Combustivel"].ToString();
            automovel.Cor               = reader["Cor"].ToString();
            automovel.DescricaoGaragem  = reader["DescricaoGaragem"].ToString();
            automovel.DescricaoMarca    = reader["DescricaoMarca"].ToString();
            automovel.DescricaoModelo   = reader["DescricaoModelo"].ToString();
            
            
            automovel.GaragemID         = (int)reader["GaragemID"];
            automovel.MarcaID           = (int)reader["MarcaID"];
            automovel.ModeloID          = (int)reader["ModeloID"];
            automovel.Observacoes       = reader["Observacoes"].ToString();
            automovel.Pasta             = reader["Pasta"].ToString();
            Int16.TryParse(reader["Portas"].ToString(), out shortAux);
            automovel.Portas = shortAux;
            shortAux = 0;
            automovel.Preco             = Convert.ToDouble(reader["Preco"]);

            Int32.TryParse(reader["Quilometragem"].ToString(), out intAux);
            automovel.Quilometragem = intAux;
            intAux = 0;
            automovel.Venda             = (bool)reader["Venda"];

            return automovel;
        }

        private List<objAutomovel> objLista(IDataReader reader)
        {
            List<objAutomovel> automoveis = new List<objAutomovel>();
            while (reader.Read())
                automoveis.Add(obj(reader));
            return automoveis;
        }  
    }
}